package com.feib.stms.service.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

import javax.sql.DataSource;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import com.feib.stms.dao.jdbc.SqlQueryProvider;
import com.feib.stms.util.UserUtil;

public class ActionLogContentExcelExportor {
	
	
	protected Logger logger = LoggerFactory.getLogger(this.getClass());

	protected DataSource dataSource;
	
	/**
	 * 資料查詢SQL
	 */
	protected SqlQueryProvider sqlQueryProvider;

	/**
	 * 暫存目錄
	 */
	protected String temporaryBaseDirectory;

	/**
	 * 目錄符號
	 */
	protected String separator = File.separator;
	
	/**
	 * Sheet Name
	 */
	protected String sheetName;
	
	/**
	 * 內容 Title 
	 */
	protected String titleName;
	
	/**
	 * 檔案名稱
	 */
	protected String fileName;
	
	/**
	 * 欄位Title 名稱
	 */
	protected String[] headers;
	
	public ActionLogContentExcelExportor(DataSource dataSource, SqlQueryProvider sqlQueryProvider, String[] headers, String temporaryBaseDirectory, String sheetName)
	{
		this(dataSource, sqlQueryProvider, headers, temporaryBaseDirectory, sheetName, null, null);		
	}

	public ActionLogContentExcelExportor(DataSource dataSource, SqlQueryProvider sqlQueryProvider, String[] headers, String temporaryBaseDirectory, String sheetName, String titleName, String fileName) 
	{
		super();
		this.dataSource = dataSource;
		this.sqlQueryProvider = sqlQueryProvider;
		this.temporaryBaseDirectory = temporaryBaseDirectory;
		this.sheetName = sheetName;
		this.headers = headers;
		
		this.titleName = titleName;
		this.fileName = fileName;
	}

	public String export() throws Exception
	{
		if (StringUtils.isEmpty(fileName))
			fileName = this.sheetName;

		if (StringUtils.isEmpty(titleName))
			titleName = this.sheetName;
		
		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可

		//CreationHelper createHelper = wb.getCreationHelper();

		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet(sheetName);

		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);

		// 行數
		int rownum = 0;

		// title row
		// 創建一行並放一些單元格到該行中，行的索引是以0開始的
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints(33);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue(titleName);
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //14 cell


		// printDate row
		// 證券業者
		Row printDateRow = sheet.createRow(rownum++);
		printDateRow.setHeightInPoints((float) 22.5);
		Cell securitiesCell = printDateRow.createCell(0);
		securitiesCell.setCellValue("證券業者：" + "亞東證券");
		securitiesCell.setCellStyle(styles.get("title2"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$I$2"));
		//列印日期
		Cell printDateCell = printDateRow.createCell(9);        
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		printDateCell.setCellValue("製表日期/時間：" + dateFormat.format(Calendar.getInstance().getTime()));
		printDateCell.setCellStyle(styles.get("title2"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$J$2:$N$2"));

		//condition row;
		Row conditionRow = sheet.createRow(rownum++);
		conditionRow.setHeightInPoints((float) 22.5);
		/* 暫不用 */


		// header row
		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 34.5);
		Cell headerCell;
		for (int i = 0; i < headers.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(headers[i]);
			headerCell.setCellStyle(styles.get("header"));
		}

		ActionlogXlsResultSetExtractor extrator = new ActionlogXlsResultSetExtractor(sheet, rownum, styles);

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.query(sqlQueryProvider.getSql(), sqlQueryProvider.getArgs(), extrator);

		rownum = extrator.getRownum(); // 取得資料輸出後，row 所在的位置

		// 調整欄位寬度 adjust the column widths
		for (int i = 0; i < headers.length; i++) {
			sheet.autoSizeColumn((short) i,true);
		}

		// 輸出到硬碟
		String tempDownloadFilePath = temporaryBaseDirectory + separator + "excel" + separator + UserUtil.getUserGroup().getGroupNo() + separator + UserUtil.getUserId() + separator;

		logger.debug("[{}]暫存目錄:{}", sheetName, tempDownloadFilePath);

		File directory = new File(tempDownloadFilePath);
		if(!directory.exists())
		{
			FileUtils.forceMkdir(directory);
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-" + fileName + ".xls";

		FileOutputStream out;
		out = new FileOutputStream(fileFullName);
		wb.write(out);
		out.flush();
		out.close();
		
		return fileFullName;
	}

	/**
	 * Create a library of cell styles
	 */
	protected static Map<String, CellStyle> createStyles(Workbook wb) {
		Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
		CellStyle style;
		Font titleFont = wb.createFont();
		titleFont.setFontName("標楷體");
		titleFont.setFontHeightInPoints((short)24);
		style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setFont(titleFont);
		styles.put("title", style);

		Font title2Font = wb.createFont();
		title2Font.setFontName("標楷體");
		title2Font.setFontHeightInPoints((short)14);
		style = wb.createCellStyle();
		style.setFont(title2Font);
		style.setWrapText(false);//設定文字是否換行
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		styles.put("title2", style);

		Font headerFont = wb.createFont();
		headerFont.setFontName("標楷體");
		headerFont.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(headerFont);
		style.setWrapText(false);//設定文字是否換行
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("header", style);

		Font cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setWrapText(false);//設定文字是否換行
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell", style);

		style = wb.createCellStyle();
		Font cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);//設定文字大小     
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);     
		style.setWrapText(false);//設定文字是否換行
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number", style);

		style = wb.createCellStyle();
		Font cellDateFont = wb.createFont();
		cellDateFont.setFontName("Arial");
		cellDateFont.setFontHeightInPoints((short)12);
		style.setFont(cellDateFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setWrapText(false);//設定文字是否換行
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("yyyy/MM/dd"));
		styles.put("cell_date", style);
		
		
		style = wb.createCellStyle();
		Font cellDateMinFont = wb.createFont();
		cellDateMinFont.setFontName("Arial");
		cellDateMinFont.setFontHeightInPoints((short)12);
		style.setFont(cellDateMinFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setWrapText(false);//設定文字是否換行
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("hh:mm:ss"));
		styles.put("cell_date_min", style);


		return styles;
	}


}
